package workspace;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;



/**
 * <p>
 * Title: $Id: JdbcTools.java,v 1.11 2007/11/15 11:58:07 goldenb Exp $
 * </p>
 * 
 * <p>
 * Description: Generic database tools e.g. like cleaning up stuff
 * </p>
 * 
 * <p>
 * Copyright: Copyright (c) 2004
 * </p>
 * <p>
 * Company: Laverock von Schoultz
 * </p>
 * 
 * <p>
 * This code and its derivatives belong to Laverock von Schoultz and may not be copied,reproduced, amended or used in
 * any way without the permission of Laverock von Schoultz.
 * </p>
 * 
 * @author Alan Tibbetts
 * @version $Revision: 1.11 $
 */
public class JdbcTools {
    private static final String NULL_TOKEN = "NULL";

    public static Date MAX_ORACLE_DATE = null;
    static {
        Calendar calendar = Calendar.getInstance();
        calendar.set(9999, 11, 31, 23, 59, 59);
        MAX_ORACLE_DATE = calendar.getTime();
    }

    private JdbcTools() {
        // Empty constructor
    }

    public static void cleanup(Connection conn) {
        try {
            if (conn != null && !conn.isClosed()) {
                /*
                 * STU:
                 * 
                 * In the land of resin the default behaviour is that any connection returned to the pool is committed
                 * first!!!! In my mind we should only ever commit if a piece of ABP code says commit() and thats that.
                 * So what we need to do here is call rollback() before we return the connection to the pool just in
                 * case there is an outstanding database transaction awaiting to be commited or rolled back.
                 * 
                 * 
                 * However, for a CMB EJB this is not allowed. The method called within an EJB cannot ever call commit()
                 * or rollback(). The container does this for you. The only exception to this rule is when you do a
                 * sessionContext.setRollbackOnly(). Setting the EJB to work as a Bean managed transaction would help
                 * but thats missing the point of an EJB container.
                 * 
                 * Anyway a lot of chit chat but a the end of all this we want to call rollback() when running under
                 * resin and not call rollback() when running under weblogic.
                 * 
                 * I don't know how to determin whether we are running under resin or weblogic so our current solution
                 * is to call rollback() anyway and catch any error and carry on regardless.
                 */
                try {
                    conn.rollback();
                } catch (SQLException e) {
                	e.printStackTrace();
                }
            }
        } catch (SQLException e) {
        	e.printStackTrace();
        }
    }

    public static void cleanup(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void cleanup(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
        	e.printStackTrace();
        }
    }

    public static void set(PreparedStatement pst, int index, Object value) throws SQLException {

        if (value instanceof BigDecimal) {
            setBigDecimal(pst, index, (BigDecimal) value);
        } else if (value instanceof Boolean) {
            setBoolean(pst, index, (Boolean) value);
        } else if (value instanceof Timestamp) {
            setTimestamp(pst, index, (Timestamp) value);
        } else if (value instanceof Date) {
            setTimestamp(pst, index, (Date) value);
        } else if (value instanceof Double) {
            setDouble(pst, index, (Double) value);
        } else if (value instanceof Integer) {
            setInt(pst, index, (Integer) value);
        } else if (value instanceof Long) {
            setLong(pst, index, (Long) value);
        } else if (value instanceof String) {
            setString(pst, index, (String) value);
        } else {
            throw new SQLException("Invalid type for JdbcTools set method - " + value.getClass());
        }
    }

    /**
     * If value = 0 or -1 then sets null else sets value
     * 
     * @param pst
     * @param index
     * @param value
     * @throws SQLException
     */
    public static void setLong(PreparedStatement pst, int index, long value) throws SQLException {

        if (value == 0 || value == -1) {

            pst.setNull(index, java.sql.Types.NUMERIC);

        } else {

            pst.setLong(index, value);

        }

    }

    public static void setLong(PreparedStatement pst, int index, Long value) throws SQLException {

        if (value == null) {
            pst.setNull(index, java.sql.Types.NUMERIC);
        } else {
            pst.setLong(index, value);
        }
    }

    /**
     * If value = 0 or -1 then sets null else sets value
     * 
     * @param pst
     * @param index
     * @param value
     * @throws SQLException
     */
    public static void setInt(PreparedStatement pst, int index, int value) throws SQLException {

        if (value == 0 || value == -1) {

            pst.setNull(index, java.sql.Types.NUMERIC);

        } else {

            pst.setInt(index, value);

        }

    }

    /**
     * If value = 0 or -1 then sets null else sets value -1 is frequently an default value indicating a problem
     * 
     * @param pst
     * @param index
     * @param value
     * @throws SQLException
     */
    public static void setDouble(PreparedStatement pst, int index, double value) throws SQLException {

        if (Double.isInfinite(value) || Double.isNaN(value)) {
            throw new SQLException("Invalid double value - " + value);
        }
        if (value == 0 || value == -1) {

            pst.setNull(index, java.sql.Types.NUMERIC);

        } else {

            pst.setDouble(index, value);

        }

    }

    /**
     * If value = Infinite or NaN then throws an exception
     * 
     * @param pst
     * @param index
     * @param value
     * @throws SQLException
     */
    public static void setValidDouble(PreparedStatement pst, int index, double value) throws SQLException {

        if (Double.isInfinite(value) || Double.isNaN(value)) {
            throw new SQLException("Invalid double value - " + value);
        }
        pst.setDouble(index, value);

    }

    public static void setString(PreparedStatement pst, int index, String value, String defaultValue)
            throws SQLException {

        if (value == null || "".equals(value)) {
            pst.setString(index, defaultValue);
        } else {
            pst.setString(index, value);
        }

    }

    public static void setString(PreparedStatement pst, int index, String value) throws SQLException {

        if (value == null || value.length() == 0) {

            pst.setNull(index, java.sql.Types.VARCHAR);

        } else {

            pst.setString(index, value);

        }

    }

    public static void setTimestamp(PreparedStatement pst, int index, Timestamp timestamp) throws SQLException {

        if (timestamp == null) {
            pst.setNull(index, java.sql.Types.TIMESTAMP);
        } else {
            pst.setTimestamp(index, timestamp);
        }
    }

    public static void setTimestamp(PreparedStatement pst, int index, Date date) throws SQLException {

        if (date == null) {
            pst.setNull(index, java.sql.Types.TIMESTAMP);
        } else {
            pst.setTimestamp(index, new Timestamp(date.getTime()));
        }
    }

    public static void setDate(PreparedStatement pst, int index, Date date) throws SQLException {

        if (date == null) {
            pst.setNull(index, java.sql.Types.DATE);
        } else {
            pst.setDate(index, new java.sql.Date(date.getTime()));
        }
    }

    public static void setBigDecimal(PreparedStatement pst, int index, BigDecimal value) throws SQLException {
        if (value == null || value.longValue() < 0) {
            pst.setNull(index, java.sql.Types.DECIMAL);
        } else {
            pst.setBigDecimal(index, value);

        }
    }

    public static void setBoolean(PreparedStatement pst, int index, Boolean value) throws SQLException {
        if (value == null) {
            pst.setNull(index, java.sql.Types.BOOLEAN);
        } else {
            pst.setBoolean(index, value);

        }
    }

    /**
     * Gets the Version attribute of the object
     * 
     * @return The Version value
     */
    public static String getVersion() {

        String version = "$Revision: 1.11 $";

        // Extract the version number without the word "Revision" stuff...
        int startPosition = version.indexOf(" ");
        int endPosition = version.indexOf(" ", startPosition + 1);
        return version.substring(startPosition + 1, endPosition);
    }

    /**
     * <p>
     * Title: Base classes for ABP
     * </p>
     * <p>
     * Description: Converts a java Date into an integer 'date only' i.e. YYYYMMDD
     * </p>
     * <p>
     * Copyright: Copyright (c) 2001
     * </p>
     * <p>
     * Company: LVS
     * </p>
     * 
     * @author unascribed
     * @version 1.0
     */
    public static class DateOnly {

        protected static int[] calMonths = new int[] {
                -1, // so that january is at index 1!
                Calendar.JANUARY, Calendar.FEBRUARY, Calendar.MARCH, Calendar.APRIL, Calendar.MAY, Calendar.JUNE,
                Calendar.JULY, Calendar.AUGUST, Calendar.SEPTEMBER, Calendar.OCTOBER, Calendar.NOVEMBER,
                Calendar.DECEMBER };

        Calendar cal;

        int year;
        int month;
        int day;

        public DateOnly(Date dateAndTime) {
            cal = Calendar.getInstance();

            cal.setTime(dateAndTime);
            this.year = cal.get(Calendar.YEAR);
            this.month = getMonthNumber(cal.get(Calendar.MONTH));
            this.day = cal.get(Calendar.DAY_OF_MONTH);

        }

        /**
         * formats a calendar month code for use in a dateonly field - this way if january is actually stored as month 0
         * in a Calendar, we get number 1 instead, and this even puts months in order if the calendar codes are out of
         * order. Returns -1 if the month code is unrecognised.
         * 
         * @param calendarMonth the calendar month code
         * @return an ordinal number for the month as in a standard english calendar, starting with 1 for january
         */
        public static int getMonthNumber(int calendarMonth) {
            for (int i = 0; i < calMonths.length; i++) {
                if (calendarMonth == calMonths[i]) {
                    return i;
                }
            }
            return -1;
        }

        public static int getCalendarMonth(int month) {
            if (month < 1 || month > 12) {
                return -1;
            }
            return calMonths[month];
        }

        public int getDateOnly() {
            final int SCALE_MONTH = 100;
            final int SCALE_YEAR = 100 * SCALE_MONTH;

            return year * SCALE_YEAR + month * SCALE_MONTH + day;
        }

        @Override
        public boolean equals(Object o) {
            if (o.getClass() == Date.class) {
                Date dateAndTime = (Date) o;

                cal.setTime(dateAndTime);
                return cal.get(Calendar.YEAR) == year && cal.get(Calendar.MONTH) == month
                        && cal.get(Calendar.DAY_OF_MONTH) == day;
            }
            return false;
        }
    }

    public static boolean getBoolean(ResultSet rs, String column) throws SQLException {
        if (rs.getInt(column) == 1) {
            return true;
        }
        return false;
    }

    public static boolean getBoolean(ResultSet rs, int columnIndex) throws SQLException {
        if (rs.getInt(columnIndex) == 1) {
            return true;
        }
        return false;
    }

    public static Double getDouble(ResultSet rs, String column) throws SQLException {
        String value = rs.getString(column);
        if (rs.wasNull() || value == null || value.length() == 0) {
            return null;
        }
        return new Double(value);
    }

    public static Double getDouble(ResultSet rs, int columnIndex) throws SQLException {
        String value = rs.getString(columnIndex);
        if (rs.wasNull() || value == null || value.length() == 0) {
            return null;
        }
        return new Double(value);
    }

    public static Date getTimestamp(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(columnIndex);
        if (rs.wasNull() || timestamp == null) {
            return null;
        }
        return new Date(timestamp.getTime());
    }

    public static Date getTimestamp(ResultSet rs, String column) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(column);
        if (rs.wasNull() || timestamp == null) {
            return null;
        }
        return new Date(timestamp.getTime());
    }

    public static String getTimeOnlyAsString(String time) {
        if (time == null) {
            return null;
        }
        String result = time;
        result = "00000" + result;
        result = result.substring(result.length() - 6);
        return result;
    }

    /**
     * Used to extract the real connection object (for example an Oracle connection object). This is typically needed
     * when using Oracle specific JDBC features.
     * 
     * @param connection
     * @return a 'naked' connection instance
     * @throws SessionException This is will be wrapping the various reflection based exceptions that could occur while
     *             trying to extract the real connection.
     */
 

    public static void setCollectionAsString(PreparedStatement ps, int index, Collection<?> collection, String separator)
            throws SQLException {

        setCollectionAsString(ps, index, collection, separator, false);
    }

    public static void setCollectionAsString(PreparedStatement ps, int index, Collection<?> collection,
            String separator, boolean allowNulls) throws SQLException {

        if (collection == null || collection.size() == 0) {
            ps.setNull(index, java.sql.Types.VARCHAR);
        } else {
            StringBuilder sb = new StringBuilder();
            for (Object listItem : collection) {
                if (sb.length() > 0) {
                    sb.append(separator);
                }
                if (allowNulls && listItem == null) {
                    sb.append(NULL_TOKEN);
                } else {
                    // if allowNulls = false AND listItem == null NPE should be thrown
                    sb.append(listItem.toString());
                }
            }

            ps.setString(index, sb.toString());
        }
    }

    public static List<Integer> getStringAsIntegerList(ResultSet rs, String column, String separator)
            throws SQLException {
        return getStringAsIntegerList(rs.getString(column), rs, separator);
    }

    public static List<Integer> getStringAsIntegerList(ResultSet rs, int index, String separator) throws SQLException {
        return getStringAsIntegerList(rs.getString(index), rs, separator);
    }

    private static List<Integer> getStringAsIntegerList(String value, ResultSet rs, String separator)
            throws SQLException {
        if (rs.wasNull() || value == null || value.length() == 0) {
            return null;
        }
        String[] tokens = value.split(separator);
        List<Integer> result = new ArrayList<Integer>(tokens.length);
        for (String token : tokens) {
            if (NULL_TOKEN.equals(token)) {
                result.add(null);
            } else {
                result.add(Integer.parseInt(token));
            }
        }

        return result;
    }

    public static List<BigDecimal> getStringAsBigDecimalList(ResultSet rs, String column, String separator)
            throws SQLException {
        return getStringAsBigDecimalList(rs.getString(column), rs, separator);
    }

    public static List<BigDecimal> getStringAsBigDecimalList(ResultSet rs, int index, String separator)
            throws SQLException {
        return getStringAsBigDecimalList(rs.getString(index), rs, separator);
    }

    private static List<BigDecimal> getStringAsBigDecimalList(String value, ResultSet rs, String separator)
            throws SQLException {
        if (rs.wasNull() || value == null || value.length() == 0) {
            return null;
        }
        String[] tokens = value.split(separator);
        List<BigDecimal> result = new ArrayList<BigDecimal>(tokens.length);
        for (String token : tokens) {
            if (NULL_TOKEN.equals(token)) {
                result.add(null);
            } else {
                result.add(new BigDecimal(token));
            }
        }

        return result;
    }

    public static Long getReferenceLong(ResultSet rs, int columnIndex) throws SQLException {
        long value = rs.getLong(columnIndex);
        if (rs.wasNull()) {
            return null;
        }
        return new Long(value);
    }

    public static Long getReferenceLong(ResultSet rs, String columnName) throws SQLException {
        long value = rs.getLong(columnName);
        if (rs.wasNull()) {
            return null;
        }
        return new Long(value);
    }

    public static void setReferenceLong(PreparedStatement ps, int index, Long value) throws SQLException {
        if (value == null) {
            ps.setNull(index, java.sql.Types.NUMERIC);
        } else {
            ps.setLong(index, value);
        }
    }

    public static Integer getReferenceInteger(ResultSet rs, int columnIndex) throws SQLException {
        int value = rs.getInt(columnIndex);
        if (rs.wasNull()) {
            return null;
        }
        return new Integer(value);
    }

    public static Integer getReferenceInteger(ResultSet rs, String columnName) throws SQLException {
        int value = rs.getInt(columnName);
        if (rs.wasNull()) {
            return null;
        }
        return new Integer(value);
    }

    public static void setReferenceInteger(PreparedStatement ps, int index, Integer value) throws SQLException {
        if (value == null) {
            ps.setNull(index, java.sql.Types.NUMERIC);
        } else {
            ps.setInt(index, value);
        }
    }

    public static Boolean getReferenceBoolean(ResultSet rs, int columnIndex) throws SQLException {
        int value = rs.getInt(columnIndex);
        if (rs.wasNull()) {
            return null;
        }
        return value == 1;
    }

    public static Boolean getReferenceBoolean(ResultSet rs, String columnName) throws SQLException {
        int value = rs.getInt(columnName);
        if (rs.wasNull()) {
            return null;
        }
        return value == 1;
    }

    public static void setReferenceBoolean(PreparedStatement ps, int index, Boolean value) throws SQLException {
        if (value == null) {
            ps.setNull(index, java.sql.Types.NUMERIC);
        } else {
            ps.setInt(index, value ? 1 : 0);
        }
    }

    public static <T extends Enum<T>> T getEnum(ResultSet rs, int columnIndex, Class<T> enumType) throws SQLException {
        String value = rs.getString(columnIndex);
        if (rs.wasNull()) {
            return null;
        }
        return Enum.valueOf(enumType, value);
    }

    public static <T extends Enum<T>> T getEnum(ResultSet rs, String columnName, Class<T> enumType) throws SQLException {
        String value = rs.getString(columnName);
        if (rs.wasNull()) {
            return null;
        }
        return Enum.valueOf(enumType, value);
    }

    public static <T extends Enum<T>> void setEnum(PreparedStatement ps, int index, T value) throws SQLException {
        if (value == null) {
            ps.setNull(index, java.sql.Types.VARCHAR);
        } else {
            ps.setString(index, value.toString());
        }
    }

}
